BEGIN;

-- 创建 object 表
CREATE TABLE IF NOT EXISTS object (
    id INT AUTO_INCREMENT PRIMARY KEY,
    obj_url VARCHAR(255) NOT NULL,
    type INT,
    upload_time DATETIME,
    delete_time DATETIME
);

-- 创建 videos 表
CREATE TABLE IF NOT EXISTS videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    obj_id INT,
    cover INT,
    title VARCHAR(255),
    cover_url VARCHAR(255),
    reship BOOLEAN,
    label VARCHAR(255),
    introduce TEXT,
    user_info INT,
    is_offline BOOLEAN
);

-- 创建 interaction 表
CREATE TABLE IF NOT EXISTS interaction (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT,
    like_count INT,
    comment_count INT,
    share_count INT,
    collect_count INT
);

-- 创建 likes 表
CREATE TABLE IF NOT EXISTS likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_id INT,
    type INT,
    user_id INT,
    time DATETIME
);

-- 创建 comment 表
CREATE TABLE IF NOT EXISTS comment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT,
    user_id INT,
    parent_id INT,
    content TEXT,
    time DATETIME
);

-- 创建 barrage 表
CREATE TABLE IF NOT EXISTS barrage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT,
    user_id INT,
    content TEXT,
    time DATETIME
);

-- 创建 userinfo 表
CREATE TABLE IF NOT EXISTS userinfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    tel VARCHAR(20),
    gender INT,
    avater VARCHAR(255),
    birth DATETIME,
    deletedat DATETIME,
    role VARCHAR(50)
);

-- 创建email_codes表
CREATE TABLE IF NOT EXISTS email_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    Code INT NOT NULL,
    Expiry DATETIME NOT NULL
);

COMMIT;